/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DAO.util.ConectaBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Matricula;

/**
 *
 * @author Alex
 */
public class MatriculaDAO {

    public boolean matricular(Matricula matricula) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "insert into matricula(id_aluno,status) values (?,1)";

            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, matricula.getAluno().getId());

            System.out.println(sql);
            pstmt.execute();

            conexao.close();
            return true;

        } catch (SQLException sql) {

            return false;

        }
    }

    public ArrayList<Matricula> buscarTodas(String nome) throws ClassNotFoundException {
        ArrayList<Matricula> matriculas = new ArrayList<Matricula>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from matricula as m, aluno as a  where a.nome ilike ?  and m.id_aluno=a.id  and m.status=1;";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setString(1, nome + "%");
            ResultSet rs = pst.executeQuery();
            AlunoDAO alunoDAO = new AlunoDAO();

            while (rs.next()) {
                Matricula matricula = new Matricula();
                matricula.setMatricula(rs.getInt("matricula"));
                matricula.setAluno(alunoDAO.buscarPessoaByCodigo(rs.getInt("id_aluno")));
                matriculas.add(matricula);
            }
            conexao.close();
            return matriculas;

        } catch (SQLException sql) {
            return null;
        }

    }

    public Matricula buscarByMatricula(int numeroMatricula) throws ClassNotFoundException, SQLException {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select * from matricula where matricula=? and status=1");
        sql.setInt(1, numeroMatricula);
        ResultSet rs = sql.executeQuery();

        Matricula matricula = new Matricula();

        AlunoDAO alunoDAO = new AlunoDAO();
        while (rs.next()) {

            matricula.setMatricula(rs.getInt("matricula"));
            matricula.setAluno(alunoDAO.buscarPessoaByCodigo(rs.getInt("id_aluno")));
            System.out.println("teste nome do aluno 2 "+matricula.getAluno().getNome());

        }
        conexao.close();
        return matricula;
    }

    public Matricula buscarPorAluno(int codigo) throws ClassNotFoundException, SQLException {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select * from matricula where id_aluno=? and status=1");
        sql.setInt(1, codigo);
        ResultSet rs = sql.executeQuery();

        Matricula matricula = new Matricula();

        AlunoDAO alunoDAO = new AlunoDAO();
        while (rs.next()) {

            matricula.setMatricula(rs.getInt("matricula"));
            matricula.setAluno(alunoDAO.buscarPessoaByCodigo(rs.getInt("id_aluno")));

        }
        conexao.close();
        return matricula;
    }

    public Matricula buscarIdPorAluno(int codigo) throws ClassNotFoundException, SQLException {
        Connection conexao = ConectaBanco.getConexao();
        PreparedStatement sql = conexao.prepareStatement("select matricula from matricula where id_aluno=? and status=1");
        sql.setInt(1, codigo);
        ResultSet rs = sql.executeQuery();

        Matricula matricula = new Matricula();

        while (rs.next()) {

            matricula.setMatricula(rs.getInt("matricula"));

        }
        conexao.close();
        return matricula;
    }

    public boolean marcarInativo(int id) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "update matricula set status=0 where matricula=?";

            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, id);

            pstmt.execute();

            conexao.close();
            return true;
        } catch (SQLException sql) {

            return false;

        }
    }
}
